package mall.dao;

import mall.model.bo.MallAskGoodsMsgBO;
import mall.model.vo.*;
import mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/*
 *
 * @author Yonni
 * @version 1.8.0_201
 * @date 2020-06-01 10:54
 *
 * */
public class MallIndexDaoImpl implements MallIndexDao {
    @Override
    public List<MallShowGoodsVO> getAllGoods() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<MallShowGoodsVO> query = null;
        try {
            query = queryRunner.query("select id, img, name, price, typeId, stockNum from goods",
                    new BeanListHandler<MallShowGoodsVO>(MallShowGoodsVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public List<MallShowGoodsVO> getGoodsByType(String typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<MallShowGoodsVO> query = null;
        try {
            query = queryRunner.query("select id, img, name, price, typeId, stockNum from goods where typeId = ?",
                    new BeanListHandler<MallShowGoodsVO>(MallShowGoodsVO.class),
                    typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public List<MallIndexSearchVO> searchGoods(String keyword) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<MallIndexSearchVO> query = null;
        try {
            query = queryRunner.query("select id, img, name, price, typeId from goods where name like ?",
                    new BeanListHandler<MallIndexSearchVO>(MallIndexSearchVO.class),
                    "%" + keyword + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public MallGoodsInfoVO getGoodsInfo(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        MallGoodsInfoVO query = null;
        try {
            query = queryRunner.query("select img, name, `desc`, typeId, price as unitPrice from goods where id = ?",
                    new BeanHandler<MallGoodsInfoVO>(MallGoodsInfoVO.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public List<SpecsInfoVO> getspecsInfoVOs(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<SpecsInfoVO> query = null;
        try {
            query = queryRunner.query("select id, specName, stockNum, unitPrice from spec where goodsId = ?",
                    new BeanListHandler<SpecsInfoVO>(SpecsInfoVO.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public List<MallGetGoodsMsgVO> getGoodsMsg(String id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<MallGetGoodsMsgVO> query = null;
        try {
            query = queryRunner.query("select id, content, nickname as asker, createtime as time, replyContent, replyCreatetime from contents where goodsId = ?",
                    new BeanListHandler<MallGetGoodsMsgVO>(MallGetGoodsMsgVO.class),
                    id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public List<MallShowCommentVO> getGoodsComment(String goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<MallShowCommentVO> query = null;
        try {
            query = queryRunner.query("select nickname, score, id, specName, comment, time, userId from comments where goodsId = ?",
                    new BeanListHandler<MallShowCommentVO>(MallShowCommentVO.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public String calculateRate(String goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Object rate = null;
        try {
            rate = queryRunner.query("select (select count(*) from comments where score = 100 and goodsId = ?)/(select count(*) from comments where goodsId = ?)",
                    new ScalarHandler<>(), goodsId, goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (rate == null) {
            return null;
        }
        return rate.toString();
    }

    @Override
    public String getUserIdFromUser(String token) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Object query = null;
        try {
            query = queryRunner.query("select id from user where nickname = ?",
                    new ScalarHandler<>(), token);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query.toString();
    }

    @Override
    public String getGoodsNameFromGoods(String goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Object query = null;
        try {
            query = queryRunner.query("select name from goods where id = ?",
                    new ScalarHandler<>(), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query.toString();
    }

    @Override
    public void askGoodsMsg(MallAskGoodsMsgBO goodsMsgBO, String userId, String goodsName) {
        //获取当前时间
        java.util.Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String curDate = dateFormat.format(date);
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into contents values (null,?,?,?,null,?,?,?,?,null)",
                    userId, goodsMsgBO.getGoodsId(), goodsMsgBO.getMsg(), 1,
                    curDate, goodsName, goodsMsgBO.getToken());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
